/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cz.muni.fi.pb138.videolibrary.database;

import com.sun.org.apache.xml.internal.serializer.OutputPropertiesFactory;
import cz.muni.fi.pb138.videolibrary.exception.ElementAlreadyExistsException;
import cz.muni.fi.pb138.videolibrary.exception.WrongNumberOfElementsException;
import java.io.File;
import java.io.IOException;
import java.net.URI;
import java.util.ArrayList;
import java.util.NoSuchElementException;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import org.odftoolkit.simple.SpreadsheetDocument;
import org.odftoolkit.simple.table.Table;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;

/**
 *
 * @author Martin Hatina, Dominika Hoďovská
 */
public class DatabaseManager implements IDatabaseManager {

    /* --------------------------------------- */
    /* ------------ public methods ----------- */
    /* --------------------------------------- */
    private Document doc;
    private Long mediumId;
    private Long categoryId;
    private String defaultLanguage;

    /**
     * @brief Konštruktor
     * @param uri URI XML súboru pomocou ktorého sa získa databáza
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    DatabaseManager(URI uri) throws ParserConfigurationException, SAXException, IOException, WrongNumberOfElementsException {
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        factory.setValidating(false);
        factory.setNamespaceAware(false);
        DocumentBuilder builder = factory.newDocumentBuilder();
        try {
            doc = builder.parse(uri.toString());
        } catch (SAXParseException ex) {
            doc = builder.newDocument();
            initDatabase();
        }

        mediumId = getLastMediumId() + 1;
        categoryId = getLastCategoryId() + 1;
        defaultLanguage = "cz";
    }

    /**
     * @brief Konštruktor
     * @param file XML súbor z ktorého sa získa databáza
     * @throws ParserConfigurationException
     * @throws SAXException
     * @throws IOException
     */
    public DatabaseManager(File file) throws ParserConfigurationException, SAXException, IOException, WrongNumberOfElementsException {
        this(file.toURI());
    }

    /**
     * @brief serializácia do XML
     * @param output výstup
     * @throws TransformerConfigurationException
     * @throws TransformerException
     * @throws IOException
     */
    @Override
    public void serializetoXML(URI output) throws IOException, TransformerConfigurationException, TransformerException {
        TransformerFactory factory = TransformerFactory.newInstance();
        Transformer transformer = factory.newTransformer();
        transformer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");
        transformer.setOutputProperty(OutputKeys.INDENT, "yes");
        transformer.setOutputProperty(OutputPropertiesFactory.S_KEY_INDENT_AMOUNT, "4");

        DOMSource source = new DOMSource(doc);
        StreamResult result = new StreamResult(output.toString());
        transformer.transform(source, result);
    }

    @Override
    public void serializetoXML(File output) throws IOException, TransformerConfigurationException, TransformerException {
        serializetoXML(output.toURI());
    }

    @Override
    public String getDefaultLanguage() {
        return defaultLanguage;
    }

    @Override
    public void setDefaultLanguage(String defaultLanguage) {
        this.defaultLanguage = defaultLanguage;
    }

    /**
     * @brief @param medium
     * @throws WrongNumberOfElementsException
     * @throws ElementAlreadyExistsException
     */
    @Override
    public void addMedium(Medium medium) throws XPathExpressionException, WrongNumberOfElementsException, ElementAlreadyExistsException {
        NodeList mediumList = getMediaElement().getElementsByTagName("medium");
        for (int i = 0; i < mediumList.getLength(); i++) {
            Medium tmp = null;
            if (medium.equals(tmp = createMedium((Element) mediumList.item(i)))) {
                throw new ElementAlreadyExistsException(String.valueOf(tmp.getId()));
            }
        }

        Element mediumElement = doc.createElement("medium");
        Element title = doc.createElement("title");
        title.setTextContent(medium.getTitle());
        mediumElement.appendChild(title);

        if (medium.getAuthor() != null && !medium.getAuthor().isEmpty()) {
            Element author = doc.createElement("author");
            author.setTextContent(medium.getAuthor());
            mediumElement.appendChild(author);
        }

        if (medium.getContent() != null && !medium.getContent().isEmpty()) {
            Element content = doc.createElement("content");
            content.setTextContent(medium.getContent());
            mediumElement.appendChild(content);
        }

        medium.setId(mediumId++);
        mediumElement.setAttribute("id", String.format("%d", medium.getId()));

        ArrayList<String> languages = medium.getAllLanguage();
        Element languagesElement = doc.createElement("languages");
        mediumElement.appendChild(languagesElement);
        if (languages != null) {
            if (languages.isEmpty()) {
                Element language = doc.createElement("language");
                language.setTextContent(defaultLanguage);
                languagesElement.appendChild(language);
            } else {
                for (int i = 0; i < languages.size(); i++) {
                    Element language = doc.createElement("language");
                    language.setTextContent(medium.getAllLanguage().get(i));
                    languagesElement.appendChild(language);
                }
            }
        }

        ArrayList<String> categories = medium.getAllCategory();
        if (categories != null) {
            for (int i = 0; i < categories.size(); i++) {
                ArrayList<Category> categoryList = null;
                Category category = null;
                if (!(categoryList = findCategory(categories.get(i))).isEmpty()) {
                    for (Category c : categoryList) {
                        if (c.getTitle().equals(categories.get(i))) {
                            category = c;
                        }
                    }
                    addToCategory(medium, category);
                } else {
                    category = new Category(categories.get(i));
                    category.addMedium(medium);
                    addCategory(category);
                }
            }
        }

        getMediaElement().appendChild(mediumElement);
    }

    /**
     * @brief vyhladá a zmaže dané médium ak existuje
     * @param medium objekt na zmazanie
     * @throws WrongNumberOfElementsException
     * @throws NoSuchElementException
     */
    @Override
    public void deleteMedium(Medium medium) throws NoSuchElementException, WrongNumberOfElementsException, XPathExpressionException {
        Element deleteElement = null;
        NodeList mediumList = getMediaElement().getElementsByTagName("medium");
        for (int i = 0; i < mediumList.getLength(); i++) {
            if (medium.getId() == Long.parseLong(((Element) mediumList.item(i)).getAttribute("id"))) {
                deleteElement = (Element) mediumList.item(i);
            }
        }

        if (deleteElement == null) {
            throw new NoSuchElementException("Element: " + medium.toString() + "does not exist");
        }

        removeFromCategories(medium);
        getMediaElement().removeChild((Node) deleteElement);
    }

    /**
     * @brief aktualizuje daný objekt
     * @param medium objekt určený na modifikáciu
     */
    @Override
    public void updateMedium(Medium medium) throws NoSuchElementException, WrongNumberOfElementsException {
        Element mediumElement = getMediumElement(medium.getId());

        NodeList tmp = mediumElement.getElementsByTagName("title");
        if (tmp.getLength() != 1) {
            throw new WrongNumberOfElementsException("Num. of title element: expected: 1, actual: " + tmp.getLength());
        } else if (!tmp.item(0).getTextContent().equals(medium.getTitle())) {
            tmp.item(0).setTextContent(medium.getTitle());
        }

        tmp = mediumElement.getElementsByTagName("author");
        switch (tmp.getLength()) {
            case 0: {
                Element author = doc.createElement("author");
                author.setTextContent(medium.getAuthor());
                mediumElement.appendChild(author);
                break;
            }
            case 1:
                tmp.item(0).setTextContent(medium.getAuthor());
                break;
            default:
                throw new WrongNumberOfElementsException("Num. of author element: expected: 0/1, actual: " + tmp.getLength());
        }

        tmp = mediumElement.getElementsByTagName("content");
        switch (tmp.getLength()) {
            case 0: {
                Element content = doc.createElement("content");
                content.setTextContent(medium.getContent());
                mediumElement.appendChild(content);
                break;
            }
            case 1:
                tmp.item(0).setTextContent(medium.getContent());
                break;
            default:
                throw new WrongNumberOfElementsException("Num. of content element: expected: 0/1, actual: " + tmp.getLength());
        }

        tmp = mediumElement.getElementsByTagName("language");
        if (mediumElement.getElementsByTagName("languages").getLength() != 1) {
            throw new WrongNumberOfElementsException("Num. of languages element: expected: 1, actual: " + tmp.getLength());
        }
        Element languagesElement = (Element) mediumElement.getElementsByTagName("languages").item(0);
        while (languagesElement.getFirstChild() != null) {
            languagesElement.removeChild(languagesElement.getFirstChild());
        }
        ArrayList<String> list = medium.getAllLanguage();
        for (int i = 0; i < list.size(); i++) {
            Element l = doc.createElement("language");
            l.setTextContent(list.get(i));
            languagesElement.appendChild(l);
        }
    }

    /**
     * @brief vyhladá médium podla názvu
     * @param title názov hladaného média
     * @throws NoSuchElementException
     */
    @Override
    public ArrayList<Medium> findMedium(String title) throws XPathExpressionException, NoSuchElementException, WrongNumberOfElementsException {
        NodeList media = getMediaElement().getElementsByTagName("medium");
        ArrayList<Medium> list = new ArrayList<>();
        for (int i = 0; i < media.getLength(); i++) {
            if (media.item(i).getTextContent().toLowerCase().contains(title.toLowerCase())) {
                Medium medium = createMedium((Element) media.item(i));
                list.add(medium);
            }
        }

        if (list.isEmpty()) {
            throw new NoSuchElementException("Element with title: " + title + " does not exist");
        }

        return list;
    }

    /**
     * @brief získanie dostupných médií
     */
    @Override
    public ArrayList<Medium> getAllMedia() throws XPathExpressionException, WrongNumberOfElementsException {
        NodeList media = getMediaElement().getElementsByTagName("medium");
        ArrayList<Medium> list = new ArrayList<>();
        for (int i = 0; i < media.getLength(); i++) {
            Medium medium = createMedium((Element) media.item(i));
            list.add(medium);
        }

        return list;
    }

    /**
     * @brief vyhladá médium podla id
     * @param id identifikátor média
     * @throws NoSuchElementException
     */
    @Override
    public Medium getMedium(long id) throws XPathExpressionException, NoSuchElementException, WrongNumberOfElementsException {
        return createMedium(getMediumElement(id));
    }

    /**
     * @brief vyhladá tag médium podla id
     * @param id identifikátor média
     * @throws NoSuchElementException
     */
    private Element getMediumElement(long id) throws NoSuchElementException, WrongNumberOfElementsException {
        Element medium = null;
        NodeList mediumList = getMediaElement().getElementsByTagName("medium");
        for (int i = 0; i < mediumList.getLength(); i++) {
            if (id == Long.parseLong(((Element) mediumList.item(i)).getAttribute("id"))) {
                medium = (Element) mediumList.item(i);
            }
        }

        if (medium == null) {
            throw new NoSuchElementException("Element medium with id: " + id + " does not exist");
        }

        return medium;
    }

    /**
     * @brief pridanie kategórie
     * @param category objekt kategórie ktorý sa pridá
     */
    @Override
    public void addCategory(Category category) throws XPathExpressionException, WrongNumberOfElementsException, ElementAlreadyExistsException {
        NodeList categoryList = doc.getElementsByTagName("category");
        for (int i = 0; i < categoryList.getLength(); i++) {
            if (category.equals(createCategory((Element) categoryList.item(i)))) {
                throw new ElementAlreadyExistsException("Category already exist");
            }
        }

        Element categoryElement = doc.createElement("category");
        category.setId(categoryId++);
        categoryElement.setAttribute("id", String.format("%d", category.getId()));
        categoryElement.setAttribute("title", category.getTitle());

        ArrayList<Medium> media = category.getAllMedia();
        if (media != null) {
//            for (Medium curr : media) {
            for (int i = 0; i < media.size(); i++) {
                Element medium = doc.createElement("medium");
                medium.setAttribute("id", String.format("%d", media.get(i).getId()));
                categoryElement.appendChild(medium);
            }
        }
        Element parent = (Element) doc.getElementsByTagName("categories").item(0);
        parent.appendChild(categoryElement);
    }

    /**
     * @brief zmazanie kategórie
     * @param category objekt kategórie ktorý sa maže
     */
    @Override
    public void deleteCategory(Category category) throws XPathExpressionException {
        Node deleteCategory = null;
        String expression = "//category[@id = " + category.getId() + "]";
        deleteCategory = xPathEvaluation(expression).item(0);
        if (deleteCategory.getChildNodes() != null) {
            //TODO
        }
        doc.getElementsByTagName("categories").item(0).removeChild(deleteCategory);
    }

    /**
     * @brief modifikácia kategórie
     * @param category objekt kategórie ktorý sa modifikuje
     */
    @Override
    public void updateCategory(Category category) throws XPathExpressionException, NoSuchElementException {
        Element categoryElement = (Element) xPathEvaluation("//category[@id = " + category.getId() + "]").item(0);
        if (categoryElement == null) {
            throw new NoSuchElementException("Element category: " + category.getId() + " does not exist");
        }

        categoryElement.setAttribute("title", category.getTitle());

    }

    /**
     * @brief vyhladanie a vrátenie kategórie podla id
     * @param id identifikátor kategórie
     */
    @Override
    public Category getCategory(long id) throws NoSuchElementException, XPathExpressionException, WrongNumberOfElementsException {
        NodeList nodeList = null;
        String expression = "/library/categories/category[@id=" + id + "]";
        nodeList = xPathEvaluation(expression);
        if (nodeList.getLength() == 0) {
            throw new NoSuchElementException("Category element with id = " + id
                    + " does not exist");
        }
        return createCategory((Element) nodeList.item(0));
    }

    /**
     * @return @brief vyhladanie kategórie pomocou názvu
     * @param title názov kategórie
     */
    @Override
    public ArrayList<Category> findCategory(String title) throws XPathExpressionException, WrongNumberOfElementsException {
        NodeList categories = doc.getElementsByTagName("category");
        ArrayList<Category> list = new ArrayList<>();
        for (int i = 0; i < categories.getLength(); i++) {
            if (((Element) categories.item(i)).getAttribute("title").toLowerCase().contains(title.toLowerCase())) {
                Category category = createCategory((Element) categories.item(i));
                list.add(category);
            }
        }
        if (list.isEmpty()) {
            throw new NoSuchElementException("Element with title: " + title + " does not exist");
        }

        return list;
    }
    
    /**
     * @brief pridanie média do danej kategórie
     * @param medium médium pridávané do danej kategórie
     * @param category kategória pridávaného média
     */
    @Override
    public void addToCategory(Medium medium, Category category) throws XPathExpressionException, WrongNumberOfElementsException {
        Element mediumElement = doc.createElement("medium");
        mediumElement.setAttribute("id", Long.toString(medium.getId()));

        NodeList categories = null;
        String exp = "//category[@id = '" + category.getId() + "']";
        categories = xPathEvaluation(exp);
        if (categories.getLength() != 1) {
            throw new WrongNumberOfElementsException("More than one or none category element with id = " + category.getId());
        }
        medium.addCategory(category.getTitle());
        category.addMedium(medium);
        categories.item(0).appendChild(mediumElement);
    }

    /**
     * @brief odobranie média z danej kategórie
     * @param medium médium odoberáne z danej kategórie
     */
    @Override
    public void removeFromCategories(Medium medium) throws XPathExpressionException {
        NodeList categories = null;
        String expression = "//category[medium/@id= " + medium.getId() + "]";
        categories = xPathEvaluation(expression);
        for (int i = 0; i < categories.getLength(); i++) {
            expression = "//category[@id = " + ((Element) categories.item(i)).getAttribute("id") + "]/medium[@id = " + medium.getId() + "]";
            Node deleteMedium = xPathEvaluation(expression).item(0);
            if (deleteMedium != null) {
                categories.item(i).removeChild(deleteMedium);
            }
        }
    }

    /**
     * @return všetky kategórie
     * @brief získanie všetkých dostupných kategórií
     */
    @Override
    public ArrayList<Category> getAllCategories() throws XPathExpressionException, WrongNumberOfElementsException {
        ArrayList<Category> list = new ArrayList<>();
        NodeList categories = doc.getElementsByTagName("category");
        for (int i = 0; i < categories.getLength(); i++) {
            Category category = createCategory((Element) categories.item(i));
            list.add(category);
        }
        return list;
    }

    /**
     * @brief import médií zo súboru odf
     * @param odf súbor z ktorého sa importuje
     */
    @Override
    public void importFrom(File odf) {
        SpreadsheetDocument outputOdt;
        try {
            outputOdt = SpreadsheetDocument.loadDocument(odf);
            int cnt = outputOdt.getSheetCount();
            for (int i = 0; i < cnt; i++) {
                Table sheet = outputOdt.getSheetByIndex(i);
                Category category = null;
                if (!sheet.getTableName().toLowerCase().contains("Sheet".toLowerCase())) {
                    addCategory(category = new Category(sheet.getTableName()));
                } else {
                    continue;
                }

                int columnCnt = sheet.getColumnCount();
                int rowCnt = sheet.getRowCount();
                for (int j = 1; j < rowCnt; j++) {
                    if (sheet.getCellByPosition(0, j).getStringValue().isEmpty()) {
                        break;
                    }

                    Medium medium = null;
                    String title = null;
                    String author = null;
                    for (int k = 1; k < columnCnt; k++) {
                        String columnName = sheet.getCellByPosition(k, 0).getStringValue();
                        if (!sheet.getCellByPosition(k, j).getStringValue().isEmpty()) {
                            if (columnName.equalsIgnoreCase("autor")) {
                                author = sheet.getCellByPosition(k, j).getStringValue();
                                k++;
                            }
                            if (title == null)
                                title = sheet.getCellByPosition(k, j).getStringValue();
                            else
                                title += ", " + sheet.getCellByPosition(k, j).getStringValue();
                        }

                        if (sheet.getCellByPosition(k, 0).getStringValue().isEmpty()
                                && sheet.getCellByPosition(k, 1).getStringValue().isEmpty()) {
                            columnCnt = k;
                        }
                    }
                    if (title != null) {
                        try {                        
                            medium = new Medium(title);
                            medium.addLanguage(defaultLanguage);
                            medium.setAuthor(author);
                            addMedium(medium);
                            addToCategory(medium, category);
                        } catch (ElementAlreadyExistsException ex) {
                            addToCategory(getMedium(Long.parseLong(ex.getMessage())), category);
                        }
                    }
                }
            }
        } catch (Exception ex) {
            Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /* --------------------------------------- */
    /* ----------- private methods ----------- */
    /* --------------------------------------- */
    /**
     * @brief inicializácia databázy
     */
    private void initDatabase() throws ParserConfigurationException {

        if (doc.getElementsByTagName("library").getLength() == 0) {
            Element library = doc.createElement("library");
            doc.appendChild(library);
        }

        if (doc.getElementsByTagName("media").getLength() == 0) {
            Element library = (Element) doc.getElementsByTagName("library").item(0);
            library.appendChild(doc.createElement("media"));
        }

        if (doc.getElementsByTagName("categories").getLength() == 0) {
            Element library = (Element) doc.getElementsByTagName("library").item(0);
            library.appendChild(doc.createElement("categories"));
        }
    }

    private Element getMediaElement() throws WrongNumberOfElementsException {
        NodeList media = doc.getElementsByTagName("media");
        if (media.getLength() != 1) {
            throw new WrongNumberOfElementsException("Num. of media element: expected: 1, actual: " + media.getLength());
        }
        return (Element) media.item(0);
    }

    /**
     * @return id posledného média
     * @brief getter id posledného média
     */
    private long getLastMediumId() throws WrongNumberOfElementsException {
        long id = 0;

        NodeList medium = getMediaElement().getElementsByTagName("medium");
        for (int i = 0; i < medium.getLength(); i++) {
            Element tmp = (Element) medium.item(i);
            if (id < Long.parseLong(tmp.getAttribute("id"))) {
                id = Long.parseLong(tmp.getAttribute("id"));
            }
        }

        return id;
    }

    /**
     * @return id poslednej kategórie
     * @brief getter id poslednej kategórie
     */
    private long getLastCategoryId() {
        long id = 0;

        NodeList categories = doc.getElementsByTagName("category");
        for (int i = 0; i < categories.getLength(); i++) {
            Element tmp = (Element) categories.item(i);
            if (id < Long.parseLong(tmp.getAttribute("id"))) {
                id = Long.parseLong(tmp.getAttribute("id"));
            }
        }
        return id;
    }

    /**
     * @brief vytvorenie a zavednie média do db
     * @param item generalizovaný objekt média
     */
    private Medium createMedium(Element item) throws XPathExpressionException {
        String title = item.getElementsByTagName("title").item(0).getTextContent();
        Medium medium = new Medium(title);

        medium.setId(Long.parseLong(item.getAttribute("id")));
        if (item.getElementsByTagName("author").getLength() != 0) {
            String author = item.getElementsByTagName("author").item(0).getTextContent();
            medium.setAuthor(author);
        } else {
            medium.setAuthor("");
        }

        if (item.getElementsByTagName("content").getLength() != 0) {
            String content = item.getElementsByTagName("content").item(0).getTextContent();
            medium.setContent(content);
        } else {
            medium.setContent("");
        }

        NodeList language = item.getElementsByTagName("language");
        for (int i = 0; i < language.getLength(); i++) {
            medium.addLanguage(language.item(i).getTextContent());
        }
        NodeList categories = null;
        String exp = "//category[medium/@id = '" + medium.getId() + "']";
        categories = xPathEvaluation(exp);
        for (int i = 0; i < categories.getLength(); i++) {
            medium.addCategory(((Element) categories.item(i)).getAttribute("title"));
        }

        return medium;
    }

    private Category createCategory(Element item) throws XPathExpressionException, WrongNumberOfElementsException {
        if (item == null) {
            return null;
        }
        String title = item.getAttribute("title");
        Category category = new Category(title);

        category.setId(Long.parseLong(item.getAttribute("id")));

        NodeList categoryMedia = item.getElementsByTagName("medium");
        for (int i = 0; i < categoryMedia.getLength(); i++) {
            try {
                Medium medium = getMedium(Long.parseLong(((Element) categoryMedia.item(i)).getAttribute("id")));
                category.addMedium(medium);
            } catch (NoSuchElementException ex) {
                Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return category;
    }

    @Override
    public void removeMediumFromCategory(Medium medium, Category category) throws XPathExpressionException {
        String expression = "//category[@id=" + category.getId() + "]/medium[@id=" + medium.getId() +"]";
        NodeList deleteElement = null;
        deleteElement = xPathEvaluation(expression);
        if (deleteElement == null || deleteElement.getLength() == 0) {
            throw new NoSuchElementException("There is not medium " + medium.toString() + " in category " + category.toString());
        }
        expression = "//category[@id=" + category.getId() + "]";
        NodeList parentCategory = xPathEvaluation(expression);
        parentCategory.item(0).removeChild(deleteElement.item(0));
    }

    private NodeList xPathEvaluation(String expression) throws XPathExpressionException {
        XPathFactory factory = XPathFactory.newInstance();
        XPath xpath = factory.newXPath();
        return (NodeList) xpath.evaluate(expression, doc, XPathConstants.NODESET);
    }
}
